package uk.ac.cam.echo.server.integrationtests;

import static org.junit.Assert.*;

import java.sql.SQLException;
import java.sql.Statement;

import junit.framework.Assert;

import org.junit.Test;

import uk.ac.cam.echo.messages.SecurityMessage;
import uk.ac.cam.echo.messages.UserMessage;
import uk.ac.cam.echo.server.Database;

/**
 * @author Tom Smith
 */
public class TestUserTable extends TestDatabase {

	@Test(expected=SQLException.class)
	/**
	 * Insert duplicate FacebookID in database.
	 */
	public void duplicateFacebookID() throws SQLException {
		// Execute SQL statement.
		Statement sqlStatement = super.m_Connection.createStatement();
		sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('1','Joe','Bloggs','Worker','1832');");
		sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('76','John','Smith','Guest','1832');");
		sqlStatement.close();

		// Commit changes to file, and release DB locks.
		super.m_Connection.commit();

		fail("Two users with same FacebookID were inserted.");
	}

	@Test(expected=SQLException.class)
	/**
	 * Insert duplicate userID in database.
	 */
	public void duplicateUserID() throws SQLException {
		// Execute SQL statement.
		Statement sqlStatement = super.m_Connection.createStatement();
		sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('1','Joe','Bloggs','Worker','34');");
		sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('1','John','Smith','Guest','7');");
		sqlStatement.close();

		// Commit changes to file, and release DB locks.
		super.m_Connection.commit();

		fail("Two users with same UserID were inserted.");
	}
	
	@Test
	/**
	 * Can retrieve user details from database.
	 */
	public void getUserDetails() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblUser + " VALUES('1','Joe','Bloggs','Worker','0');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();
			
			UserMessage user = super.m_db.getUserDetails("1");
			
			Assert.assertEquals("Joe", user.getForename());
			Assert.assertEquals("Bloggs", user.getSurname());
			Assert.assertEquals("0", user.getFacebookID());

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}

	@Test
	/**
	 * Can retrieve user details from database.
	 */
	public void getUserSecurityDetails() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblUser + " VALUES('1','Joe','Bloggs','Worker','joebloggs');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();
			
			SecurityMessage user = super.m_db.getSecurityDetails("1");
			
			Assert.assertEquals("Joe", user.getForename());
			Assert.assertEquals("Bloggs", user.getSurname());
			Assert.assertEquals("Worker", user.getUserType());
			Assert.assertEquals("joebloggs", user.getFacebookID());
			Assert.assertFalse(user.getAllowed());

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}
	
	@Test
	/**
	 * Insert multiple different users into the database.
	 */
	public void insertMultipleUsers() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			for (int i = 0; i < 2500; i++) {
				sqlStatement.execute("INSERT INTO " + Database.tblUser + " VALUES('" + i + "','','','Guest','" + i + "');");
			}
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}
	
	@Test
	/**
	 * Insert one user into the database.
	 */
	public void insertUser() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblUser + " VALUES('1','Joe','Bloggs','Worker','34');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}
	
	@Test
	/**
	 * Can retrieve user details from database.
	 */
	public void setFacebookIDTest() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblUser + " VALUES('1','Joe','Bloggs','Worker','');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();
			
			super.m_db.setFacebookID("1", "joebloggs");
			
			UserMessage user = super.m_db.getUserDetails("1");
			
			Assert.assertEquals("Joe", user.getForename());
			Assert.assertEquals("Bloggs", user.getSurname());
			Assert.assertEquals("joebloggs", user.getFacebookID());

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}

}
